# -*- coding: utf-8 -*-
from subprocess import Popen, PIPE
import re
import os
import csv
import traceback
import logging
logging.basicConfig(level=logging.DEBUG, format=" [%(asctime)s] - [%(levelname)s] - [%(message)s]")
# logging.basicConfig(filename="runlog.log", level=logging.DEBUG, format=" [%(asctime)s] - [%(levelname)s] - [%(message)s]")
# logging.disable(logging.DEBUG)


class class_oracle():
    def __init__(self, user, password, ip, port, server):
        sqlplus_connect_config = user + "/" + password + "@" + ip + ":" + port + "/" + "orcl"
        self.sqlplus = Popen(["sqlplus", sqlplus_connect_config], stdout=PIPE, stdin=PIPE, stderr=PIPE)  # 模拟win系统上cmd命令 sqlplus输入
        logging.debug("class_oracle类，__init__函数".format(sqlplus_connect_config))

    # 创建触发器语句
    def create_triggere(self, table_user, table_name, list_no_idx_table_field):
        """
        :param table_user: 表用户名
        :param table_name: 表名
        :param list_no_idx_table_field: 表非索引字段
        :return: 
        """
        logging.debug("创建触发器"+table_name)
        # 编写触发器生语句
        try:
            str_trigger_name = "TRIGGER_TEST_" + table_name.upper() # 触发器名
            str_trigger_table_user_name = table_user + "." + table_name # 表名
            str_trigger_field_1, str_trigger_field_2= get_trigger_field(field_list=list_no_idx_table_field) # 字段处理
            str_trigger_all = "CREATE OR REPLACE TRIGGER " + str_trigger_name + "\n" \
                            + "  BEFORE" +"\n" \
                            + "    UPDATE OF " + str_trigger_field_1 + "\n" \
                            + "    ON " + str_trigger_table_user_name + "\n" \
                            + "FOR EACH ROW" + "\n" \
                            + "DECLARE" + "\n" \
                            + "BEGIN" + "\n" \
                            + "    CASE" + "\n" \
                            + str_trigger_field_2 \
                            + "        NULL;" + "\n" \
                            + "    END CASE;" + "\n" \
                            + "END " + str_trigger_name + ";" + "\n" \
                            + "/"
            logging.debug("创建语句：" + str_trigger_all)
        except:
            logging.error("创建触发器语句失败，详细信息：\n" + traceback.format_exc())
        try:
            self.exec_create_trigger(str_create_trigger=str_trigger_all)
        except:
            logging.error("创建触发器失败，详细信息：\n" + traceback.format_exc())

    # 通过SqlPlus执行触发器创建
    def exec_create_trigger(self, str_create_trigger):
        str_trigger_all = str_create_trigger
        self.sqlplus.stdin.write(bytes(str_trigger_all, encoding='gbk'))
        (out, err) = self.sqlplus.communicate()
        if self.sqlplus.returncode != 0:
            logging.error("创建触发器报错，详细信息：\n" + str(err, encoding='gbk'))
        else:
            logging.debug("创建触发器成功，详细信息：\n" + str(out, encoding='gbk'))

    # 执行SQL比对校验
    def exec_update(self, table_user, table_name, no_idx_field_value, idx_field_value):
        """
        :param table_user: 表用户(type=str)
        :param table_name: 表名(type=str)
        :param no_idx_field_value: 非索引字段(type=dict) 
        :param idx_field_value: 索引字段(type=dict)
        :return: <1>错误号0-比对成功、1-未找到、2-比对失败、3-执行语句失败
                 <2>应答信息返回
        """
        set_sql_str = ""
        where_sql_str = ""
        logging.debug("对比前显示非索引字段{0}".format(no_idx_field_value))
        logging.debug("对比前显示索引字段{0}".format(idx_field_value))
        for key in no_idx_field_value: # 拼非索引字段与值，字典里面只有一对记录
            set_sql_str += key + " = " + "'" + no_idx_field_value[key] + "'" + " "
        for key in idx_field_value:  # 拼索引字段与值，字典里面可能有多条记录
            where_sql_str += key + " = " + "'" + idx_field_value[key] + "'" + " AND "
        update_sql_str = "UPDATE " + table_user + "." + table_name \
                       + " SET " \
                       + set_sql_str \
                       + " WHERE " \
                       + where_sql_str \
                       + " 1=1 " \
                       + ";"
        logging.debug("执行比对语句SQL:{0}".format(update_sql_str))
        self.sqlplus.stdin.write(bytes(update_sql_str, encoding='gbk'))
        (out, err) = self.sqlplus.communicate()
        if self.sqlplus.returncode != 0:
            logging.error("执行比对语句失败，详细信息：\n" + str(err, encoding='gbk'))
            return_no = 3
            return_info = "执行比对语句失败"
            return return_no, return_info
        else:
            logging.debug("执行比对语句成功，详细信息：\n" + str(out, encoding='gbk'))
            # <1>错误号0-正常、1-未找到、2-比对失败 <2>应答信息返回
            return_no, return_info = filter_info(info=str(out, encoding='gbk'))
            return return_no, return_info

    # 禁用触发器
    def disable_trigger(self, p_table_user, p_table_name):
        disable_trigger_str = "alter table " + p_table_user + "." + p_table_name + " disable all triggers;"
        self.sqlplus.stdin.write(bytes(disable_trigger_str, encoding='gbk'))
        (out, err) = self.sqlplus.communicate()

    # 启用触发器
    def enable_trigger(self, p_table_user, p_table_name):
        disable_trigger_str = "alter table " + p_table_user + "." + p_table_name + " enable all triggers;"
        self.sqlplus.stdin.write(bytes(disable_trigger_str, encoding='gbk'))
        (out, err) = self.sqlplus.communicate()

# 循环处理字段并返回,供触发器创建
def get_trigger_field(field_list):
    """
    :param field_list: 非索引字段 list
    :return: 
    """
    str_result_1 = ""
    str_result_2 = ""
    # 判断字段列表是否为空
    if field_list:
        # 遍历字段列表，并循环处理
        for i, str_field in enumerate(field_list):
            if i == 0:
                str_result_1 = str_field
            else:
                str_result_1 = str_result_1 + "," + str_field
            str = "        WHEN UPDATING('" + str_field + "') THEN" + "\n" \
                + "            IF :old." + str_field + " <> :new." + str_field + " THEN" + "\n" \
                + "                dbms_output.put_line('字段:" + str_field + "比对不一致,原值为:'||:OLD." + str_field + "||',目标值为:'||:NEW." + str_field + ");" + "\n" \
                + "                raise_application_error('-20001','字段:" + str_field + "比对不一致,原值:'||:OLD." + str_field + "||',目标值:'||:NEW." + str_field + ");" + "\n" \
                + "            END IF;" + "\n"
            str_result_2 = str_result_2 + str
        return str_result_1, str_result_2
    # 如果字段列表为空
    else:
        return " ", "NULL;"

# 过滤出应答信息
def filter_info(info):
    """
    :param info: sqlplus处理应答信息
    :return: <1>错误号0-比对成功、1-未找到、2-比对失败 
             <2>应答信息返回
    """
    if "ORA-20001" in info:
        return_no = 2
        return_info = re.findall("ORA-20001:([\s\S]*)ORA-06512", info) # 返回list
        return_info = return_info[0]
        logging.debug("比对失败")
        logging.debug(re.findall("ORA-20001:([\s\S]*)ORA-06512", info))
    elif "已更新0行" in info:
        return_no = 1
        return_info = "根据索引字段未找到该数据"
        logging.debug("根据索引字段未找到该数据")
    else:
        return_no = 0
        return_info = "比对成功"
    return return_no, return_info